from dbutils.pooled_db import PooledDB
from openai import OpenAI
import time
import json
import pymysql
import datetime
from flask import Flask, request, jsonify
app = Flask(__name__)

# 配置连接池
pool = PooledDB(
    creator=pymysql,  # 使用 pymysql 作为数据库驱动
    maxconnections=10,  # 连接池允许的最大连接数
    mincached=2,  # 初始化时连接池中至少创建的空闲连接
    maxcached=5,  # 连接池中最多闲置的连接
    blocking=True,  # 连接池中如果没有可用连接后是否阻塞等待
    host='10.228.81.233',
    user='db_user',
    password='7%zCxZ0s21FL2jmJ',
    database='comment_management',
    port=30020,
    charset='utf8mb4'
)

# 从连接池中获取连接
def get_connection():
    return pool.connection()

def close_connection():
    return pool.close()

#  ai接口回评
def feedback_ai():
    ############## 查询数据
    conn = get_connection()
    cursor = conn.cursor()
    select_sql = f"SELECT suborder_id,comment_txt,add_comment FROM comment_information WHERE ( AI_rate_level IS NULL OR AI_rate_level = '' ) AND evaluation_status_of_comments = FALSE AND ( comment_txt IS NOT NULL AND comment_txt != '' )  AND rate_level = 1"
    update_sql = f"UPDATE comment_information SET AI_rate_level = %s WHERE suborder_id = %s "
    # 执行批量插入
    rows = []
    try:
        cursor.execute(select_sql)
        rows = cursor.fetchall()
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        # 关闭游标和连接
        cursor.close()
        conn.close()

    ############## 更新数据
    conn = get_connection()
    cursor = conn.cursor()
    try:
        for row in rows:
            ai_rate_level = feedback_customer(f"{row[1]}{row[2]}")
            print(f"{ai_rate_level}=首评：{row[1]},追评:{row[2]}")
            cursor.execute(update_sql, (1 if ai_rate_level == '好评' else -1, row[0]))
            conn.commit()
            break
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        # 关闭游标和连接
        cursor.close()
        conn.close()


def feedback_customer(user_content, system_content = '服装客服人员,只要有任何负面评价则定义为差评，出现价格贵、尺码颜色有差异定义为差评，尺码有差异的情况，否则定义为好评，只返回好评或者差评'):

    # system_content = '你是服装客服人员,只要有任何负面评价则定义为差评，否则定义为好评，只返回好评或者差评'
    # user_content = '考虑了很久，之前上新没买，这次一套买的，上身很舒服，很适合南方的宝宝，买的是灰豆绿，很耐脏，就是收到的时候有点皱皱的'
    # user_content2 = '170 125斤买最大码 里面可以穿毛衣 百搭 穿运动裤深色的运动裤穿着也可以 料子比较软摸着也不硬 版型显瘦'
    # 异常处理
    client = OpenAI(
        # 若没有配置环境变量，请用百炼API Key将下行替换为：api_key="sk-xxx",
        # api_key=os.getenv("DASHSCOPE_API_KEY"),
        api_key="sk-5fdb6ed65aa246c4a16dbf07fa699be2",
        base_url="https://dashscope.aliyuncs.com/compatible-mode/v1",
    )
    start_time = time.time()
    completion = client.chat.completions.create(
        model="qwen-max-latest",  # 此处以qwen-plus为例，可按需更换模型名称。模型列表：https://help.aliyun.com/zh/model-studio/getting-started/models
        messages=[
            # {'role': 'system', 'content': '你是服装客服人员,只要有任何负面评价则定义为差评，否则定义为好评，只返回好评或者差评'},
            # {'role': 'user', 'content': '考虑了很久，之前上新没买，这次一套买的，上身很舒服，很适合南方的宝宝，买的是灰豆绿，很耐脏，就是收到的时候有点皱皱的'}],
            {'role': 'system', 'content': system_content},
            {'role': 'user', 'content': user_content}
            # ,
            # {'role': 'system', 'content': system_content},
            # {'role': 'user', 'content': user_content2}
        ],

    )
    json_data = completion.model_dump_json()
    # 将 JSON 字符串解析为 Python 字典
    data = json.loads(json_data)
    # 提取 result 对象中的 score 属性
    evaluation = data['choices'][0]['message']['content']
    end_time = time.time()
    elapsed_time = end_time - start_time
    print(evaluation, f"{datetime.datetime.now()},耗时: {elapsed_time}秒")
    # print(json_data)
    return evaluation

# sku更新接口
@app.route('/upload_filter_skus', methods=['POST'])
def upload_filter_skus():
    # 获取查询参数
    sku_codes = request.get_json()
    conn = get_connection()
    cursor = conn.cursor()
    delete_sql = f"DELETE FROM comment_exclude_goods";
    insert_sql = f"INSERT INTO comment_exclude_goods (sku_code) VALUES (%s)"
    # 执行批量插入
    try:
        cursor.execute(delete_sql)
        cursor.executemany(insert_sql, sku_codes)
        conn.commit()  # 提交事务
        print(f"Successfully inserted {len(sku_codes)} records.")
    except Exception as e:
        conn.rollback()  # 回滚事务
        print(f"An error occurred: {e}")
    finally:
        # 关闭游标和连接
        cursor.close()
        conn.close()
    # user_content = '考虑了很久，之前上新没买，这次一套买的，上身很舒服，很适合南方的宝宝，买的是灰豆绿，很耐脏，就是收到的时候有点皱皱的'
    # isGood = FeedbackService.feedback_customer(user_content)
    return jsonify(code = 200, msg = 'success')


# 示例调用
# data = feedback_ai()
# print(data)
# user_content = '毛衣很柔软，颜色和图片一样，就是有点小贵，可以接受。'
# feedback_customer(user_content)